package com.zygh.questionnairesurvey.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zygh.questionnairesurvey.entity.Problem;
import com.zygh.questionnairesurvey.entity.vo.ProblemVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ProblemMapper extends BaseMapper<Problem> {

    /**
     * 根据警号和部门查询问题
     *
     * @param username 警号
     * @param rank     问题级别
     * @param deptId   部门id
     * @return
     */
    @Select("<script> SELECT p.*,q.warningsignal FROM questionnaire q LEFT JOIN problem p ON FIND_IN_SET(p.id,q.lowest) " +
            "<where> " +
            "<if test = 'username != null and username.length &gt; 0'  > q.warningsignal=#{username} </if> " +
            "<if test = 'deptId != null '  > and q.dept_id = #{deptId} </if> " +
            "<if test = 'rank != null '  > and p.rank = #{rank} </if> " +
            "<if test = 'pid != null '  > and p.pid = #{pid} </if> " +
            "</where>" +
            " ORDER BY  p.id asc" +
            " </script>")
    List<Problem> findProbelmById(@Param("username") String username,
                                  @Param("deptId") Integer deptId,
                                  @Param("rank") Integer rank,
                                  @Param("pid") Integer pid);


    @Select("<script>" +
            "SELECT count(p.id)  FROM questionnaire q \n" +
            "LEFT JOIN problem p ON FIND_IN_SET(p.id,q.lowest) \n" +
            "LEFT JOIN user_info u ON q.warningsignal = u.user_name \n" +
            "WHERE u.user_name IN" +
            "<foreach item='userName' collection='userName' index='index'  open='(' separator=',' close=')'>" +
            " #{userName} \n" +
            "</foreach>" +
            "AND p.id not IN (SELECT p.pid FROM questionnaire q \n" +
            "LEFT JOIN problem p ON FIND_IN_SET(p.id,q.lowest) \n" +
            "LEFT JOIN user_info u ON q.warningsignal = u.user_name \n" +
            "WHERE u.user_name IN " +
            "<foreach item='userName' collection='userName' index='index'  open='(' separator=',' close=')'>" +
            " #{userName} \n" +
            "</foreach>" +
            " and p.rank!=1) " +
            "GROUP BY q.warningsignal " +
            "</script>")
    List<Integer> countByUserName(@Param("userName") List<String> userName);

    @Select("<script>" +
            "SELECT count(*) FROM questionnaire q \n" +
            "WHERE q.lowest = ''\n" +
            "AND q.warningsignal IN " +
            "<foreach item='userName' collection='userName' index='index'  open='(' separator=',' close=')'>" +
            " #{userName} \n" +
            "</foreach>" +
            "</script>")
    Integer countLowest(@Param("userName") List<String> userName);

    /**
     * 根据警号查询该民警的问题数量
     *
     * @param id
     * @return
     */
    @Select("SELECT count(p.id)  FROM questionnaire q LEFT JOIN problem p ON FIND_IN_SET(p.id,q.lowest) " +
            "LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "WHERE  u.user_name = #{id} AND p.id not IN (SELECT p.pid FROM questionnaire q " +
            "LEFT JOIN problem p ON FIND_IN_SET(p.id,q.lowest) LEFT JOIN user_info u ON q.warningsignal = u.user_name " +
            "WHERE   u.user_name = #{id} and p.rank!=1) \n")
    int findProblemCoundById(String id);

    @Select("select * from  problem where FIND_IN_SET(id,getParentList(#{id})) ")
    List<Problem> findProblemByLastId(Integer id);


}